package com.pkuse.bjousystem.repository.school.registry;

import com.pkuse.bjousystem.dto.school.registry.RegistryDTO;
import com.pkuse.bjousystem.repository.school.SchoolTableOptions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.Collections;
import java.util.List;
import java.util.stream.IntStream;
import java.util.stream.Stream;

public class RegistryRepositoryImpl implements RegistryRepository {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @PersistenceContext
    private EntityManager em;

    @Override
    public Page<RegistryDTO> listRegistry(SchoolTableOptions options, Pageable pageable) {

        StringBuffer xkndxqSql = new StringBuffer();
        String[] ndxqArray = options.getNianDuXueQi().map(o -> o.toArray(new String[o.size()])).orElse(new String[0]);
        if (options.getNianDuXueQi().isPresent()) {
            xkndxqSql.append("and (");
            IntStream.range(0, ndxqArray.length).forEach(i -> xkndxqSql.append("(xsxkjgb.nd = :nd" + i + " and xsxkjgb.xqdm = :xqdm" + i + ") " + (i < ndxqArray.length - 1 ? "or " : "")));
            xkndxqSql.append(") ");
        }

        String dataSqlPre = "select xk.nd as nd, xk.xqdm as xqdm, xqdmb.xqmc as xqmc, xk.xxdm as xxdm, xxdmb.xxmc as xxmc, xslbdmb.xslbdm as xslbdm, xslbdmb.xslbmc as xslbmc, zydmb.zydm as zydm, zydmb.zymc as zymc, count(xsb.xh) as count " +
                "from xsb inner join ( " +
                "select xsxkjgb.nd as nd, xsxkjgb.xqdm as xqdm, xsxkjgb.xh as xh, xsxkjgb.xxdm as xxdm, xsxkjgb.bdm as bdm from xsxkjgb " +
                "where (:xxdm is null or xsxkjgb.xxdm = :xxdm) and xsxkjgb.pzh is not null " + xkndxqSql.toString() +
                "group by xsxkjgb.nd, xsxkjgb.xqdm, xsxkjgb.xh, xsxkjgb.xxdm, xsxkjgb.bdm " +
                ") as xk on xsb.xh = xk.xh " +
                "left join xxdmb on xxdmb.xxdm = xk.xxdm " +
                "left join bjxxb on bjxxb.bdm = xk.bdm " +
                "left join xslbdmb on xslbdmb.xslbdm = bjxxb.xslbdm " +
                "left join zydmb on zydmb.zydm = xsb.zydm " +
                "left join xqdmb on xqdmb.xqdm = xk.xqdm " +
                "where 1=1 ";
        String dataSqlPost = "group by xk.nd, xk.xqdm, xqdmb.xqmc, xk.xxdm, xxdmb.xxmc, xslbdmb.xslbdm, xslbdmb.xslbmc, zydmb.zydm, zydmb.zymc " +
                "order by xk.nd desc, xk.xqdm desc, xk.xxdm, xslbdmb.xslbdm, zydmb.zydm";

        String countSqlPre = "select count(1) " +
                "from xsb inner join ( " +
                "select xsxkjgb.nd as nd, xsxkjgb.xqdm as xqdm, xsxkjgb.xh as xh, xsxkjgb.xxdm as xxdm, xsxkjgb.bdm as bdm from xsxkjgb " +
                "where (:xxdm is null or xsxkjgb.xxdm = :xxdm) and xsxkjgb.pzh is not null " + xkndxqSql.toString() +
                "group by xsxkjgb.nd, xsxkjgb.xqdm, xsxkjgb.xh, xsxkjgb.xxdm, xsxkjgb.bdm " +
                ") as xk on xsb.xh = xk.xh " +
                "left join bjxxb on bjxxb.bdm = xk.bdm " +
                "where 1=1 ";
        String countSqlPost = "group by xk.nd, xk.xqdm, xk.xxdm, bjxxb.xslbdm, xsb.zydm";

        StringBuffer dataSql = new StringBuffer();
        dataSql.append(dataSqlPre);
        StringBuffer countSql = new StringBuffer();
        countSql.append(countSqlPre);

        String[] zydmArray = options.getZhuanYeMingCheng().map(o -> o.toArray(new String[o.size()])).orElse(new String[0]);
        if (options.getZhuanYeMingCheng().isPresent()) {
            Stream.of(dataSql, countSql).forEach(sb -> {
                sb.append("and xsb.zydm in (");
                IntStream.range(0, zydmArray.length).forEachOrdered(i -> sb.append(":zydm" + i + (i < zydmArray.length - 1 ? "," : "")));
                sb.append(") ");
            });
        }

        String[] xslbArray = options.getXueShengLeiBie().map(o -> o.toArray(new String[o.size()])).orElse(new String[0]);
        if (options.getXueShengLeiBie().isPresent()) {
            Stream.of(dataSql, countSql).forEach(sb -> {
                sb.append("and bjxxb.xslbdm in (");
                IntStream.range(0, xslbArray.length).forEachOrdered(i -> sb.append(":xslb" + i + (i < xslbArray.length - 1 ? "," : "")));
                sb.append(") ");
            });
        }

        dataSql.append(dataSqlPost);
        countSql.append(countSqlPost);

        Query dataQuery = em.createNativeQuery(dataSql.toString(), "RegistryDTO");
        Query countQuery = em.createNativeQuery(countSql.toString());

        dataQuery.setParameter("xxdm", options.getSchoolID().getId().orElse(null));
        countQuery.setParameter("xxdm", options.getSchoolID().getId().orElse(null));

        if (options.getZhuanYeMingCheng().isPresent()) {
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, zydmArray.length).forEachOrdered(i -> query.setParameter("zydm" + i, zydmArray[i])));
        }
        if (options.getXueShengLeiBie().isPresent()) {
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, xslbArray.length).forEachOrdered(i -> query.setParameter("xslb" + i, xslbArray[i])));
        }
        if (options.getNianDuXueQi().isPresent()) {
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, ndxqArray.length).forEachOrdered(i -> query.setParameter("nd" + i, ndxqArray[i].split(SchoolTableOptions.DELIMITER)[0])));
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, ndxqArray.length).forEachOrdered(i -> query.setParameter("xqdm" + i, ndxqArray[i].split(SchoolTableOptions.DELIMITER)[1])));
        }

        dataQuery.setFirstResult(Long.valueOf(pageable.getOffset()).intValue()); // pageable.getPageSize * pageable.getPageNumber?
        dataQuery.setMaxResults(pageable.getPageSize());

        long total = Integer.valueOf(countQuery.getResultList().size()).longValue();

        List<RegistryDTO> content  = total > pageable.getOffset() ? dataQuery.getResultList() : Collections.<RegistryDTO> emptyList();

        return new PageImpl<>(content, pageable, total);
    }
}
